<?
function get_upcomming_payments($pdiff=1,$format='html'){
	global $session_db_name,$dbh,$sid,$baseURL;
	$SQL="	SELECT rl.parent_id,
					r.id,r.reseller,r.firstname,r.lastname,r.address1,r.address2,r.city,r.state,r.zip,r.country,r.current_release_level,
					s.sale_id,
					sum((s.price-s.real_price)*(rl.link_type='union')*(s.payed='false')+s.real_price*0.1*(rl.link_type='affiliate')*(s.type='plan')*(s.ref_payed='false')) as suma
		
				FROM resellers r
				LEFT JOIN reseller_links rl on(r.id=rl.parent_id)
				LEFT JOIN sales s on(s.id=rl.child_id)

				WHERE 
					s.state='normal' and  
					PERIOD_DIFF( date_format(now(),'%Y%m'),date_format(s.date,'%Y%m') )>=$pdiff
				GROUP by rl.parent_id
				HAVING suma>=r.current_release_level
				order by r.payment_method,r.reseller
				;";
	//echo $SQL;
	if (!($result = @mysql_db_query($session_db_name, $SQL, $dbh))) return 0;
	$params[sid]=$sid;
	$params[action]="view_detailed";
	while($row=mysql_fetch_assoc($result)){
		$params[reseller_id]=$row[id];
		$params[pdiff]=$pdiff;
		if($format=='html'){
			$url="$baseURL/admin/payments/?".encode_params($params);
			$ids[]=$row[id];
			$res.="	<tr>
				<td>$row[reseller]</td>
				<td>$row[firstname]</td>
				<td>$row[lastname]</td>
				<td>$row[address1] | $row[address2]</td>
				<td>$row[current_release_level]</td>
				<td>$row[suma]</td>
				<td><input type=submit value='Screen' onClick=\"window.open('$url')\"></td>
				</tr>";
		}else {
			$arr[]=Array(
				'first_name'=>$row[firstname],
				'last_name'=>$row[lastname],
				'address1'=>$row[address1],
				'address2'=>$row[address2],
				'city'=>$row[city],
				'state'=>$row[state],
				'zip'=>$row[zip],
				'country'=>$row[country],
				'check_number'=>'',
				'amount'=>$row[suma],	
				'memo'=>'resellerspanel.com');
		}
	}
	if($format=='html'){
		$ret[res]=$res;
		$ret[ids]=$ids;
		return $ret;
	}else return $arr;
}
function get_detailed_payments($id,$pdiff=1){
	
	global $session_db_name,$dbh,$sid,$baseURL;
	$SQL ="select count(*) as count from w9 where id='$id'";
	if ($result = @mysql_db_query($session_db_name, $SQL, $dbh)){
		$row=mysql_fetch_assoc($result);
		$w9 = $row['count'];
		if($w9) $w9='yes ('.$w9.')';
		else $w9 = 'no';
	}
	$SQL="	SELECT 
			r.id,
			r.reseller,
			r.firstname,
			r.lastname,
			r.email,
			r.w9_form,
			r.phone,
			r.address1,
			r.address2,
			r.city,
			r.state as shtat,
			r.zip,
			r.country,
			r.paypal_email,
			r.swift_code,
			r.bank_name,
			r.bank_address,
			r.account_number,
			r.routing_number,
			r.account_holder,
			r.payment_method,
			r.blocked,
			r.ip,
			r.referer,
			r.date,
			r.tax_id,
			r.current_release_level,
			rl.link_type,
			r1.reseller as act_res,
			s.sale_id,
			s.client,
			s.type,
			s.price,
			s.real_price,
			s.state,
			(s.price-s.real_price)*(rl.link_type='union')*(s.payed='false')+s.real_price*0.1*(rl.link_type='affiliate')*(s.type='plan')*(s.ref_payed='false') as commissions,
			s.date

		FROM resellers r
		LEFT JOIN reseller_links rl on(r.id=rl.parent_id)
		LEFT JOIN resellers r1 on(r1.id=rl.child_id)
		LEFT JOIN sales s on(s.id=r1.id)
		WHERE 
			s.state='normal'
			and PERIOD_DIFF( date_format(now(),'%Y%m'),date_format(s.date,'%Y%m') )>=$pdiff
			and r.id='$id'
		having commissions!=0;
		";
	
	 if (!($result = @mysql_db_query($session_db_name, $SQL, $dbh))) return 0;
	
	 while($row=mysql_fetch_assoc($result)){
		if(!($row[link_type]=="affiliate" && $row[type]!="plan")){
			$res.="	<tr>
				<td>$row[act_res]</td>
				<td>$row[client]</td>
				<td>$row[link_type]</td>
				<td>$row[type]</td>
				<td>$row[state]</td>
				<td>$row[date]</td>
				<td>$row[price]</td>
				<td>$row[real_price]</td>
				<td>$row[commissions]</td>
				</tr>";
			$total+=$row[commissions];
			$price+=$row[price];
			$real_price+=$row[real_price];
			$last_row=$row;
			$transactions.="<input type=hidden name=sales[] value='$row[sale_id]|$row[link_type]'>\n";
		}
	 }
	 $transactions.="<input type=hidden name=amount value='$total'>";
	 $transactions.="<input type=hidden name='res_id' value='$id'>";
	 $res.="<tr><th align=right colspan=2>Reseller Profit:</th><td align=left colspan=10>$ $total</th><tr>";
	 $res.="<tr><th align=right colspan=2>Our profit:</th><td align=left colspan=10>$ $real_price</th><tr>";
	 $res.="<tr><th align=right colspan=2>Total Profit:</th><td align=left colspan=10>$ $price</th><tr>";

	$params[sid]=$sid;
	$params[action]='paypal_email';
	$params[res_id]=$id;
	$url_pp="?".encode_params($params);

	$row=$last_row;
	$tables[0]=$res;
	while(list($k,$v)=each($row)) if(!$v) $row[$k]="~";
	$tables[1].="
		<tr><th>Reseller:</th>         <td>$row[reseller]</td></tr>	
		<tr><th>Paypal Email:</th>     <td>$row[paypal_email] &nbsp;<a href='$url_pp' target='_blank'>change</a></tr>	
		<tr><th>firstname</th>         <td>$row[firstname]</td></tr>
		<tr><th>lastname</th>          <td>$row[lastname]</td></tr>
		<tr><th>email</th>             <td>$row[email]</td></tr>
		<tr><th>phone</th>             <td>$row[phone]</td></tr>
		<tr><th>address1</th>          <td>$row[address1]</td></tr>
		<tr><th>address2</th>          <td>$row[address2]</td></tr>
		<tr><th>city</th>              <td>$row[city]</td></tr>
		<tr><th>state</th>             <td>$row[shtat]</td></tr>
		<tr><th>zip</th>               <td>$row[zip]</td></tr>
		<tr><th>country</th>           <td>$row[country]</td></tr>
		<tr><th>swift_code             <td>$row[swift_code]</td></tr>
		<tr><th>bank_name</th>         <td>$row[bank_name]</td></tr>
		<tr><th>bank_address</th>      <td>$row[bank_address]</td></tr>
		<tr><th>account_number</th>    <td>$row[account_number]</td></tr>
		<tr><th>routing_number</th>    <td>$row[routing_number]</td></tr>
		<tr><th>account_holder</th>    <td>$row[account_holder]</td></tr>
		<tr><th>payment_method</th>    <td>$row[payment_method]</td></tr>
		<tr><th>ip</th>                <td>$row[ip]</td></tr>
		<tr><th>referer</th>           <td>$row[referer]</td></tr>
		<tr><th>date</th>              <td>$row[date]</td></tr>
		<tr><th>tax_id</th>            <td>$row[tax_id]</td></tr>
		<tr><th>W9 FORM</th>      		<td>$w9</td></tr>
		<tr><th>CURRENT STATUS</th>     <td>$row[blocked]</td></tr>
		";
	
	$tables[2]=$transactions;
	$temp_arr=Array('check'=>'Check','wire'=>'Wire Transfer','paypal'=>'Paypal','wallet'=>'Wallet');
	foreach($temp_arr as $k=>$v){
		$selected='';
		if($k==$row[payment_method]) $selected='selected';
		$tables[3].="<option value='$k' $selected>$v</option>"; 
	}
	return $tables;
}
function get_check_detailed($id){
	global $session_db_name,$dbh;
	$SQL="select firstname,lastname,address1,address2,zip,city,state,country,c.name from resellers r left join countries c on(country=c.iso2) where id='$id'";
	if (!($result = @mysql_db_query($session_db_name, $SQL, $dbh))) return 0;
	$row=mysql_fetch_assoc($result);
	$table.="<tr><td>$row[firstname] $row[lastname]<br>";
	$table.="$row[address1]<br>";
	if($row[address2]){
		$table.="$row[address2]<br>";
	}
	$table.="$row[city], $row[state] $row[zip], $row[country]</td></tr>";
	return $table;
}
function get_check_detailed_array($ids){
	global $session_db_name,$dbh;
	
	if(!is_array($ids)) exit(":error");
	$id="id='".implode("' or id='",$ids)."'";
	$SQL="select firstname,lastname,address1,address2,zip,city,state,c.name as country from resellers r left join countries c on(country=c.iso2) where $id ";
	if (!($result = @mysql_db_query($session_db_name, $SQL, $dbh))) return 0;
	include('countries.php');
	while($row=mysql_fetch_assoc($result)){

		if($row[address2]){
			$table[]="$row[firstname] $row[lastname]<br>"."$row[address1]<br>"."$row[address2]<br>"."$row[city], $row[state] $row[zip]<br> ".$row[country];
		}else{
			$table[]="$row[firstname] $row[lastname]<br>"."$row[address1]<br>"."$row[city], $row[state] $row[zip]<br> ".$row[country];
		}
	}	
	return $table;
}
function approve_payment($id,$amount,$sales,$comment,$payment_method=''){
	global $session_db_name,$dbh;
	$date=date("Y-m-d",time());	
	$SQL[]="insert into payments_history (id,issued,amount,comment,payment_method) values('$id',now(),'$amount','$comment','$payment_method')";
	//print_r($sales);
	foreach($sales as $s){
		$arr=explode("|",$s);
		if($arr[1]=="union")	$SQL[]="update sales set payed='true',payment_date='$date',payment_comment='$comment' where sale_id='$arr[0]'";
		else $SQL[]="update sales set ref_payed='true',ref_payment_date='$date' where sale_id='$arr[0]'";
		
		
	}
	//print_r($SQL);exit;
	return transaction($SQL,1);
}
function history($year,$month){	
	global $session_db_name,$dbh;
	
	$SQL="select r.reseller,issued,amount from payments_history p,resellers r where p.id=r.id and month(issued)='$month' and year(issued)='$year'";
	//echo $SQL;
	if (!($result = @mysql_db_query($session_db_name, $SQL, $dbh))) return 0;
	while($row=mysql_fetch_assoc($result)){
		$res.="<tr><td>$row[reseller]</td><td>$row[issued]</td><td>$row[amount]</td></tr>";
	}
	return $res;
}

function add_group_member($type,$parent_id,$child_id){
		global $session_db_name, $dbh;
		if($type=="affiliate"){
				$SQL1="insert into reseller_links(parent_id,child_id,link_type) values($parent_id,$child_id,'$type')";
				if (!($result = mysql_db_query($session_db_name,$SQL1, $dbh))) {
						$SQL2="update reseller_links set parent_id='$parent_id' where child_id='child_id' and link_type='affiliate'";
						if (!($result = mysql_db_query($session_db_name,$SQL2, $dbh))) return 0;
				}
		}
		if($type=="union"){
				$SQL1="update reseller_links set parent_id='$parent_id' where parent_id='$child_id'";
				if (!($result = mysql_db_query($session_db_name,$SQL1, $dbh))) return 0;
		}
		return 1;


}
function remove_group_member($type,$parent_id,$child_id){
		global $session_db_name, $dbh;
		if($type=="affiliate"){
				$SQL="delete from reseller_links where parent_id='$parent_id' and child_id='$child_id' and link_type='$type'";
		}else if($type=="union"){
				$SQL="update reseller_links set parent_id='$child_id' where parent_id='$parent_id' and child_id='$child_id' and link_type='$type'";
		}
		return mysql_db_query($session_db_name,$SQL, $dbh);

}

function get_group_details($parent_id='',$child_id=''){
        global $session_db_name, $dbh;
        if(!$parent_id && $child_id){
                $SQL="select parent_id from reseller_links where child_id='$child_id'";
                if (!($result = mysql_db_query($session_db_name,$SQL, $dbh))) return 0;
                $row=mysql_fetch_row($result);
                $parent_id=$row[parent_id];
        }
        $SQL="  SELECT  rl.parent_id,rl.child_id,link_type,r.id,r.reseller,r.firstname,r.lastname,r.email,r.address1,r.address2,r.country,r.state
                        FROM    reseller_links rl LEFT JOIN resellers r ON(rl.child_id=r.id)
                        WHERE   rl.parent_id='$parent_id';";
        if (!($result = mysql_db_query($session_db_name,$SQL, $dbh))) return 0;
        while($row=mysql_fetch_assoc($result)){
                $res[]=$row;
        }
        return $res;
}
function get_reseller_referer($child_id){
		global $session_db_name, $dbh;
		$SQL="  SELECT  rl.parent_id,rl.child_id,link_type,r.id,r.reseller,r.firstname,r.lastname,r.email,r.address1,r.address2,r.country,r.state
				 FROM    reseller_links rl LEFT JOIN resellers r ON(rl.parent_id=r.id)
				WHERE   rl.child_id='$child_id';";
	
        if (!($result = mysql_db_query($session_db_name,$SQL, $dbh))) return 0;
        while($row=mysql_fetch_assoc($result)){
                $res[]=$row;
        }
        return $res;


}
function change_pp_email($res_id,$pp_email){
               global $session_db_name, $dbh;
               $SQL=" update resellers set paypal_email='$pp_email' WHERE  id='$res_id';";
        if (!($result = mysql_db_query($session_db_name,$SQL, $dbh))) return 0;
        return 1;

}
function approve_payment_wallet($id,$amount,$sales,$comment,$payment_method=''){
        global $session_db_name,$dbh;
        $date=date("Y-m-d",time());
        $SQL[]="insert into payments_history (id,issued,amount,comment,payment_method) values('$id',now(),'$amount','$comment','$payment_method')";
        //print_r($sales);
        foreach($sales as $s){
                $arr=explode("|",$s);
                if($arr[1]=="union")    $SQL[]="update sales set payed='true',payment_date='$date',payment_comment='$comment
' where sale_id='$arr[0]'";
                else $SQL[]="update sales set ref_payed='true',ref_payment_date='$date' where sale_id='$arr[0]'";


        }
      #print_r($SQL);exit;
	include('../../login/wallet_db.html');
	if (!$dbh81) return false;
	if (!($result = mysql_db_query($session_db_name,"BEGIN", $dbh))) return 0;
	if (!$result81=pg_query($dbh81,"BEGIN")) return 0;
	$query="SELECT * FROM wallet WHERE reseller_id='$id'";
	$result81=pg_query($dbh81,$query);
	$num=pg_num_rows($result81);
	if ($num==0) {
		$query="INSERT INTO wallet VALUES ('$id','$amount','USD')";
	} else {
		$query="UPDATE wallet SET amount=amount+'$amount' WHERE reseller_id='$id' and currency='USD'";
	}
	if (!$result81=pg_query($dbh81,$query)) {
		$result = mysql_db_query($session_db_name,"ROLLBACK", $dbh);
		$result81=pg_query($dbh81,"ROLLBACK");
		return 0;
	}
	if (!$result81=pg_query($dbh81,"INSERT INTO wallet_history (status,reseller_id,currency,order_date,comment,paytype,init_amount,amount) VALUES ('available','$id','USD',now(),'$comment','profit','$amount','$amount')")) {
                $result = mysql_db_query($session_db_name,"ROLLBACK", $dbh);
                $result81=pg_query($dbh81,"ROLLBACK");
                return 0;
        }
	foreach($SQL as $q){
                if($q){
                        //echo "$q <br>";
                        if (!($result = mysql_db_query($session_db_name, $q, $dbh))){
                                //echo mysql_error($result);
                                $result = mysql_db_query($session_db_name,"ROLLBACK", $dbh);
				$result81=pg_query($dbh81,"ROLLBACK");
                                return 0;
                        }
                        if(!$type){
                                if(mysql_affected_rows($dbh) == 0){
                                        $result = mysql_db_query($session_db_name,"ROLLBACK", $dbh);
					$result81=pg_query($dbh81,"ROLLBACK");
                                        return 0;
                                }
                        }
                }
        }
	if (!($result = mysql_db_query($session_db_name,"COMMIT", $dbh))) {
		$result81=pg_query($dbh81,"ROLLBACK");
		return 0;
	} else {
		$result81=pg_query($dbh81,"COMMIT");
	}
	@pg_close($dbh81);
}

?>
